package cn.com.nes.site.module.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.UUID;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import cn.com.nes.common.util.DBUitl;
import cn.com.nes.common.util.DBUtils;
import cn.com.nes.common.util.Globals;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.DataRes;
import cn.com.nes.site.entity.PageData;

public abstract class BaseConnDaoImpl {

	@SuppressWarnings("unchecked")
	public PageData insertTableData(Connection conn, DataReq req) throws SQLException {
		PageData res = new PageData();

		PageData sqlInfo = getInsertSQL(conn, req);
		if (sqlInfo.getRetCode() != 0) {
			return sqlInfo;
		}
		final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
		final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
		update(conn, sb_field.toString(), listData.toArray());

		// 线索添加id把uuid转换成clueid
		String uuid = (String) req.getReqData().get("uuid");
		if ((String) req.getReqData().get("OperationLogId") != null
				&& (String) req.getReqData().get("OperationLogId") == "updateClueId") {
			if (StringUtils.isEmpty((String) req.getReqData().get("clueId"))) {
				req.getReqData().put("clueId", uuid);
			} else {
				req.getReqData().put("clueId", req.getReqData().get("clueId"));
			}

		}
		if ((String) req.getReqData().get("OperationLogId") != null
				&& (String) req.getReqData().get("OperationLogId") == "updateCustomerId") {
			if (StringUtils.isEmpty((String) req.getReqData().get("customerId"))) {
				req.getReqData().put("customerId", uuid);
			} else {
				req.getReqData().put("customerId", req.getReqData().get("customerId"));
			}

		}
		if ((String) req.getReqData().get("OperationLogId") != null
				&& (String) req.getReqData().get("OperationLogId") == "updateOpportunityId") {
			if (StringUtils.isEmpty((String) req.getReqData().get("opportunityId"))) {
				req.getReqData().put("opportunityId", uuid);
			} else {
				req.getReqData().put("opportunityId", req.getReqData().get("opportunityId"));
			}

		}
		if ((String) req.getReqData().get("OperationLogId") != null
				&& (String) req.getReqData().get("OperationLogId") == "updateProjectId") {
			if (StringUtils.isEmpty((String) req.getReqData().get("projectId"))) {
				req.getReqData().put("projectId", uuid);
			} else {
				req.getReqData().put("projectId", req.getReqData().get("projectId"));
			}

		}

		// 添加操作日志
		if(!"0".equals(req.getReqData().get("isLog"))){
			SaveOperatelog(req, (String) req.getReqData().get("OperationLogType"),
					(String) req.getReqData().get("OperationLogName"), sb_field + "---" + listData, conn);
		}
		
		req.getReqData().put("uuid", uuid);
		res.setRetCode(0);
		res.setRetMessage("OK");
		res.setKey(uuid);
		return res;
	}

	public PageData getDetailsByKey(Connection conn, Map<String, Object> param) throws SQLException {
		PageData res = new PageData();
		PageData sqlInfo = getbaseQuerySQL(conn, param);
		if (sqlInfo.getRetCode() != 0) {
			return sqlInfo;
		}

		res.setRetCode(9);
		res.setRetMessage("没有查询到详细数据");

		StringBuffer sb = (StringBuffer) sqlInfo.getData().get("sb");
		List<String> listData = (List<String>) sqlInfo.getData().get("listData");
		List<Map<String, Object>> list = queryForList(conn, sb.toString(), listData.toArray());
		if (list.size() == 1) {
			res.setRetCode(0);
			res.setData(list.get(0));
			res.setRetMessage("OK");
		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData updateTableData(Connection conn, DataReq req) throws SQLException {
		PageData res = new PageData();
		PageData sqlInfo = getUpdateSQL(conn, req);
		if (sqlInfo.getRetCode() != 0) {
			return sqlInfo;
		}
		final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
		final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
		update(conn, sb_field.toString(), listData.toArray());

		// 线索添加id把uuid转换成clueid
		String uuid = (String) req.getReqData().get("uuid");
		if ((String) req.getReqData().get("OperationLogId") != null
				&& (String) req.getReqData().get("OperationLogId") == "updateClueId") {
			if (StringUtils.isEmpty((String) req.getReqData().get("clueId"))) {
				req.getReqData().put("clueId", uuid);
			} else {
				req.getReqData().put("clueId", req.getReqData().get("clueId"));
			}
		}
		if ((String) req.getReqData().get("OperationLogId") != null
				&& (String) req.getReqData().get("OperationLogId") == "updateCustomerId") {
			if (StringUtils.isEmpty((String) req.getReqData().get("customerId"))) {
				req.getReqData().put("customerId", uuid);
			} else {
				req.getReqData().put("customerId", req.getReqData().get("customerId"));
			}
		}
		if ((String) req.getReqData().get("OperationLogId") != null
				&& (String) req.getReqData().get("OperationLogId") == "updateOpportunityId") {
			if (StringUtils.isEmpty((String) req.getReqData().get("opportunityId"))) {
				req.getReqData().put("opportunityId", uuid);
			} else {
				req.getReqData().put("opportunityId", req.getReqData().get("opportunityId"));
			}
		}
		if ((String) req.getReqData().get("OperationLogId") != null
				&& (String) req.getReqData().get("OperationLogId") == "updateProjectId") {
			if (StringUtils.isEmpty((String) req.getReqData().get("projectId"))) {
				req.getReqData().put("projectId", uuid);
			} else {
				req.getReqData().put("projectId", req.getReqData().get("projectId"));
			}
		}

		SaveOperatelog(req, (String) req.getReqData().get("OperationLogType"),
				(String) req.getReqData().get("OperationLogName"), sb_field + "---" + listData, conn);

		req.getReqData().put("uuid", uuid);
		res.setRetCode(0);
		res.setRetMessage("OK");
		res.setKey(uuid);
		return res;
	}

	public PageData deleteTableData(Connection conn, DataReq req) throws SQLException {
		PageData res = new PageData();
		PageData sqlInfo = deleteSQL(req);
		if (sqlInfo.getRetCode() != 0) {
			return sqlInfo;
		}
		final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
		final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
		update(conn, sb_field.toString(), listData.toArray());

		SaveOperatelog(req, (String) req.getReqData().get("OperationLogType"),
				(String) req.getReqData().get("OperationLogName"), sb_field + "---" + listData, conn);

		res.setRetCode(0);
		res.setRetMessage("OK");
		return res;
	}

	public PageData getInsertSQL(Connection conn, DataReq req) throws SQLException {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		UUID uuid = UUID.randomUUID();
		String tableName = (String) param.get("tableName");
		if (StringUtils.isEmpty((String) param.get("uuid"))) {
			param.put("uuid", uuid.toString());
		}

		StringBuffer sb_field = new StringBuffer();
		StringBuffer sb = new StringBuffer();
		StringBuffer sb_value = new StringBuffer();

		if (req.getUinfo() != null) {
			param.put("inputUser", req.getUinfo().getId());
			param.put("createUid", req.getUinfo().getId());
			param.put("modifyUid", req.getUinfo().getId());
		} else {
			param.put("createUid", "000");
			param.put("modifyUid", "000");
		}
		
		if(StringUtils.isEmpty((String)param.get("createTime"))) {
			String t = DBUtils.getCurTime();
			param.put("createTime", t);
			param.put("modifyTime", t);
		}
		

		List<String> listData = new ArrayList<String>();
		sb.delete(0, sb.length());
		sb.append(
				" select column_name column_name,is_nullable,column_type,column_comment,column_default,data_type  from INFORMATION_SCHEMA.Columns where lower(TABLE_SCHEMA)='")
				.append(Globals.DBNAME).append("' and lower(table_name)=? and extra<>'auto_increment' ");
		List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[] { tableName });
		for (Map<String, Object> data : list) {
			String column_name = data.get("column_name") == null ? (String) data.get("column_name".toUpperCase())
					: (String) data.get("column_name");
			String is_nullable = data.get("is_nullable") == null ? (String) data.get("is_nullable".toUpperCase())
					: (String) data.get("is_nullable");
			String column_comment = data.get("column_comment") == null
					? (String) data.get("column_comment".toUpperCase())
					: (String) data.get("column_comment");
			String column_default = data.get("column_default") == null
					? (String) data.get("column_default".toUpperCase())
					: (String) data.get("column_default");
			String column_value = Objects.toString(param.get(column_name), null);
			String data_type = data.get("data_type") == null ? (String) data.get("data_type".toUpperCase())
					: (String) data.get("data_type");

			if ("NO".equals(is_nullable)) {
				if (StringUtils.isBlank(column_value) && StringUtils.isBlank(column_default)) {
					res.setRetCode(9);
					res.setRetMessage(column_comment + "(" + column_name + ")不能为空！");
					return res;
				}
			}

			if (column_value != null) {
				if (data_type.indexOf("int") >= 0 || data_type.indexOf("decimal") >= 0) {
					if (!DBUtils.isNumber(column_value)) {
						res.setRetCode(9);
						res.setRetMessage(column_comment + "(" + column_name + ")类型错误！");
						return res;
					}
				}
			}

			if (!StringUtils.isBlank(column_value)) {
				sb_field.append("`").append(column_name).append("`,");
				sb_value.append("?").append(",");
				listData.add(column_value);
			}
		}
		sb_field.deleteCharAt(sb_field.length() - 1);
		sb_value.deleteCharAt(sb_value.length() - 1);
		sb_field.insert(0, "(").insert(0, "`" + tableName + "`").insert(0, "insert into ");
		sb_field.append(")values(").append(sb_value).append(")");
		Map<String, Object> data = new HashMap<String, Object>();
		data.put("sb_field", sb_field);
		data.put("listData", listData);
		res.setData(data);
		res.setRetCode(0);
		res.setRetMessage("OK");
		return res;
	}

	public PageData getUpdateSQL(Connection conn, DataReq req) throws SQLException {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		String tableName = (String) param.get("tableName");
		StringBuffer sb_field = new StringBuffer();
		StringBuffer sb = new StringBuffer();
		StringBuffer sb_where = new StringBuffer();
		List<String> listData = new ArrayList<String>();
		String keys = (String) param.get("keys");
		if (keys == null || keys.equals("")) {
			res.setRetCode(9);
			res.setRetMessage("主键不能为空！");
			return res;
		}
		String[] keylist = keys.split(";");
		for (int i = 0; i < keylist.length; i++) {
			String val = (String) param.get(keylist[i]);
			if (StringUtils.isEmpty(val)) {
				res.setRetCode(9);
				res.setRetMessage(keylist[i] + "不能为空！");
				return res;
			}
		}
		if (req.getUinfo() != null) {
			param.put("modifyUid", req.getUinfo().getId());
		} else {
			param.put("modifyUid", "000");
		}

		String t = DBUtils.getCurTime();
		param.put("modifyTime", t);

		sb_where.append(" where ");

		sb.delete(0, sb.length());
		sb.append(
				" select column_name column_name,is_nullable,column_type,column_comment,column_default,data_type  from INFORMATION_SCHEMA.Columns where lower(TABLE_SCHEMA)='")
				.append(Globals.DBNAME).append("' and lower(table_name)=? and extra<>'auto_increment' ");
		List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[] { tableName });
		for (Map<String, Object> data : list) {
			String column_name = data.get("column_name") == null ? (String) data.get("column_name".toUpperCase())
					: (String) data.get("column_name");
			String is_nullable = data.get("is_nullable") == null ? (String) data.get("is_nullable".toUpperCase())
					: (String) data.get("is_nullable");
			String column_comment = data.get("column_comment") == null
					? (String) data.get("column_comment".toUpperCase())
					: (String) data.get("column_comment");
			String column_default = data.get("column_default") == null
					? (String) data.get("column_default".toUpperCase())
					: (String) data.get("column_default");
			String column_value = Objects.toString(param.get(column_name), null);
			String data_type = data.get("data_type") == null ? (String) data.get("data_type".toUpperCase())
					: (String) data.get("data_type");

			if ("NO".equals(is_nullable)) {
				if ("".equals(column_value) && StringUtils.isBlank(column_default)) {
					res.setRetCode(9);
					res.setRetMessage(column_comment + "不能为空！");
					return res;
				}
			}

			if (column_value != null) {
				if (data_type.indexOf("int") >= 0 || data_type.indexOf("decimal") >= 0) {
					if (!DBUtils.isNumber(column_value)) {
						res.setRetCode(9);
						// TODO  本地环境时可放开
//						res.setRetMessage(column_comment + "(" + column_name + ")类型错误！");
						res.setRetMessage(column_comment + "类型错误！");
						return res;
					}
				}
			}

			if (column_value != null) {
				boolean isPK = false;
				for (String key_name : keylist) {
					if (column_name.equals(key_name)) {
						isPK = true;
						break;
					}
				}
				if (!isPK) {
					sb_field.append("`").append(column_name).append("`=?,");
					listData.add(column_value);
				}
			}
		}
		sb_field.deleteCharAt(sb_field.length() - 1);
		sb_field.insert(0, " set ").insert(0, tableName).insert(0, "update ");

		for (String column_name : keylist) {
			sb_where.append(column_name).append("=? and ");
			String column_value = (String) param.get(column_name);
			listData.add(column_value);
		}
		sb_where.append(" 1=1 ");
		sb_field.append(sb_where);

		Map<String, Object> data = new HashMap<String, Object>();
		data.put("sb_field", sb_field);
		data.put("listData", listData);
		res.setData(data);
		res.setRetCode(0);
		res.setRetMessage("OK");
		return res;
	}

	public PageData deleteSQL(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		String tableName = (String) param.get("tableName");
		StringBuffer sb_field = new StringBuffer();
		StringBuffer sb = new StringBuffer();
		StringBuffer sb_where = new StringBuffer();
		List<String> listData = new ArrayList<String>();
		String keys = (String) param.get("keys");
		if (keys == null || keys.equals("")) {
			res.setRetCode(9);
			res.setRetMessage("主键不能为空！");
			return res;
		}
		String[] keylist = keys.split(";");
		for (int i = 0; i < keylist.length; i++) {
			String val = (String) param.get(keylist[i]);
			if (StringUtils.isEmpty(val)) {
				res.setRetCode(9);
				res.setRetMessage(keylist[i] + "不能为空！");
				return res;
			}
		}

		sb_field.append(" delete from ").append(tableName).append(" where ");

		for (String column_name : keylist) {
			sb_where.append(column_name).append("=? and ");
			String column_value = (String) param.get(column_name);
			listData.add(column_value);
		}
		sb_where.append(" 1=1 ");
		sb_field.append(sb_where);

		Map<String, Object> data = new HashMap<String, Object>();
		data.put("sb_field", sb_field);
		data.put("listData", listData);
		res.setData(data);
		res.setRetCode(0);
		res.setRetMessage("OK");
		return res;
	}

	public List<Map<String, Object>> getbaseQueryData(Connection conn, Map<String, Object> param) throws SQLException {
		PageData res = new PageData();
		PageData sqlInfo = getbaseQuerySQL(conn, param);
		StringBuffer sb = (StringBuffer) sqlInfo.getData().get("sb");
		List<String> listData = (List<String>) sqlInfo.getData().get("listData");
		List<Map<String, Object>> list = queryForList(conn, sb.toString(), listData.toArray());
		return list;
	}

	public PageData getbaseQuerySQL(Connection conn, Map<String, Object> param) throws SQLException {
		PageData res = new PageData();
		String tableName = (String) param.get("tableName");
		StringBuffer sb = new StringBuffer();
		StringBuffer sb_where = new StringBuffer();
		List<String> listData = new ArrayList<String>();
		String keys = (String) param.get("keys");
		if (keys == null || keys.equals("")) {
			res.setRetCode(9);
			res.setRetMessage("主键不能为空！");
			return res;
		}
		sb_where.append(" from ").append(tableName).append(" a where ");
		String[] keylist = keys.split(";");
		sb.delete(0, sb.length());
		sb.append(
				" select lower(column_name) column_name,column_comment,CONVERT(data_type , char) data_type  from INFORMATION_SCHEMA.Columns where lower(TABLE_SCHEMA)='")
				.append(Globals.DBNAME).append("' and lower(table_name)=? ");
		List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[] { tableName });

		sb.delete(0, sb.length());
		sb.append("select ");
		for (Map<String, Object> data : list) {
			String column_name = (String) data.get("column_name");
			String data_type = (String) data.get("data_type");
			if ("timestamp".equals(data_type)) {
				sb.append("date_format(a.").append(column_name).append(",'%Y-%m-%d %T') ").append(column_name)
						.append(",");
			} else {
				sb.append("a.").append(column_name).append(",");
			}
		}
		sb.deleteCharAt(sb.length() - 1);
		for (String column_name : keylist) {
			sb_where.append(column_name).append("=?,");
			String column_value = (String) param.get(column_name);
			listData.add(column_value);
		}
		sb_where.deleteCharAt(sb_where.length() - 1);
		sb.append(sb_where);
		Map<String, Object> data = new HashMap<String, Object>();
		data.put("sb", sb);
		data.put("listData", listData);
		res.setData(data);
		res.setRetCode(0);
		res.setRetMessage("OK");
		return res;
	}

	public PageData isExists(Connection conn, DataReq req) throws SQLException {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		String tableName = (String) param.get("tableName");
		StringBuffer sb = new StringBuffer();
		StringBuffer sb_where = new StringBuffer();
		String keys = (String) param.get("keys");
		if (keys == null || keys.equals("")) {
			res.setRetCode(9);
			res.setRetMessage("主键不能为空！");
			return res;
		}
		sb.delete(0, sb.length());
		sb.append(" select count(1) cnt from  ").append(tableName);
		sb_where.append(" where ");
		String[] keylist = keys.split(";");
		for (String key_name : keylist) {
			sb_where.append(key_name).append("='").append(param.get(key_name)).append("' and ");
		}
		sb_where.append(" 1=1 ");
		sb.append(sb_where.toString());
		sb = DBUtils.sql_check(sb);
		int cnt = queryForInt(conn, sb.toString(), new Object[] {});
		if (cnt == 0) {
			res.setRetCode(0);
			res.setRetMessage("OK");
		} else {
			res.setRetCode(9);
			res.setRetMessage(keys + "已经存在！");
		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData SaveSyslog(DataReq req, String yhmk, String remark, String sqllog, Connection conn) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("系统日志添加失败");
		param.put("tableName", "t_logs");
		param.put("id", req.getUinfo().getId());
		param.put("yhbs", req.getUinfo().getUsername());
		param.put("yhmk", yhmk);
		param.put("remark", remark);
		param.put("sqllog", sqllog);
		try {
			PageData sqlInfo = getInsertSQL(conn, req);
			if (sqlInfo.getRetCode() != 0) {
				return sqlInfo;
			}
			StringBuffer log_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			List<String> log_listData = (List<String>) sqlInfo.getData().get("listData");
			update(conn, log_field.toString(), log_listData.toArray());
			res.setKey((String) param.get("uuid"));
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	@RequestMapping(method = RequestMethod.POST, value = "/getUUID")
	public @ResponseBody PageData getUUID(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws Exception {
		DataRes req_Status = DBUtils.setRemoteVisit(request, response, true);
		PageData res = new PageData();
		if (req_Status.getRetCode() != 0) {
			res = new PageData();
			res.setRetCode(req_Status.getRetCode());
			res.setRetMessage(req_Status.getRetMessage());
			return res;
		}

		UUID uuid = UUID.randomUUID();
		res.setKey(uuid.toString());

		res.setRetCode(0);
		res.setRetMessage("OK");
		return res;
	}

	// 根据父ID查询部门下所有子部门
	public String getAllNextCompanyId(Connection conn, String p_companyId) throws SQLException {
		String companyId = "";
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT companyId FROM ( ");
		sb.append(" SELECT ");
		sb.append(" @ids AS _ids, ");
		sb.append(
				" ( SELECT @ids := GROUP_CONCAT( companyId ) FROM t_company WHERE FIND_IN_SET( p_companyId, @ids ) ) AS cids, ");
		sb.append(" @l := @l + 1 AS LEVEL ");
		sb.append(" FROM ");
		sb.append(" t_company,( SELECT @ids := ?, @l := 0 ) b ");
		sb.append(" WHERE ");
		sb.append(" @ids IS NOT NULL ");
		sb.append(" ) id, ");
		sb.append(" t_company DATA ");
		sb.append(" WHERE ");
		sb.append(" FIND_IN_SET( DATA.companyId, ID._ids ) ");
		sb.append(" ORDER BY ");
		sb.append(" LEVEL,companyId ");
		List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[] { p_companyId });
		if (list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				if (i == 0) {
					companyId = "'" + list.get(i).get("companyId").toString() + "'";
				} else {
					companyId = companyId + "," + "'" + list.get(i).get("companyId").toString() + "'";
				}
			}
		}
		return companyId;
	}

	// 根据已有子部门ID生成下一个ID
	public String getNextId(Connection conn, String p_companyId) throws SQLException {
		String aaaa = "";
		String companyId = "";
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT companyId from ( ");
		sb.append(" SELECT companyId from t_company where p_companyId=? ");
		sb.append(" UNION ");
		sb.append(" SELECT departmentId companyId from t_department where p_companyId=? ");
		sb.append(" )x order by companyId desc limit 1 ");
		List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[] { p_companyId, p_companyId });
		if (list.size() > 0) {
			companyId = list.get(0).get("companyId").toString().substring(
					list.get(0).get("companyId").toString().length() - 2,
					list.get(0).get("companyId").toString().length());
		} else {
			companyId = "00";
		}
		int nextCompanyId = Integer.parseInt(companyId) + 1;
		if (nextCompanyId > 9) {
			companyId = String.valueOf(nextCompanyId);
		} else {
			companyId = String.valueOf("0" + nextCompanyId);
		}
		if ("00".equals(p_companyId)) {
			aaaa = companyId;
		} else {
			aaaa = p_companyId + companyId;
		}
		return aaaa;
	}

	// 根据行政区划代码截取查询所用代码
	public String getQueryXzqh(Connection conn, String xzqhdm, String name) throws SQLException {
		String result = "";
		StringBuffer sb = new StringBuffer();
		StringBuffer sbadd = new StringBuffer();
		if (xzqhdm == null || xzqhdm.equals("")) {
			return "";
		}
		String[] xzqhArr = xzqhdm.split(";");
		for (int i = 0; i < xzqhArr.length; i++) {
			sb.delete(0, sb.length());
			sb.append(" select XZQHJB from T_XZQH where XZQHDM= ? ");
			List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[] { xzqhArr[i] });
			if (list.size() > 0) {
				int jb = Integer.parseInt(list.get(0).get("XZQHJB").toString());
				if (jb == 0)
					result = "";
				else if (jb == 1)
					result = xzqhArr[i].substring(0, 2);
				else if (jb == 2)
					result = xzqhArr[i].substring(0, 4);
				else if (jb == 3)
					result = xzqhArr[i].substring(0, 6);
				else if (jb == 4)
					result = xzqhArr[i].substring(0, 9);
				else if (jb == 5)
					result = xzqhArr[i];
				else if (jb == 6)
					result = xzqhArr[i];
				else
					result = xzqhArr[i];
			}
			sbadd.append(name).append(" like '").append(result).append("%' ");
			if (i < xzqhArr.length - 1) {
				sbadd.append(" or ");
			}
		}
		sbadd.insert(0, " and (").append(")");
		return sbadd.toString();
	}

	@SuppressWarnings("unchecked")
	public PageData SaveOperatelog(DataReq req, String type, String remark, String sqllog, Connection conn) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("操作日志添加失败");
		param.put("tableName", "log_operate");
		param.put("uuid", UUID.randomUUID().toString());
		param.put("type", type);
		param.put("userid", req.getUinfo().getId());
		param.put("username", req.getUinfo().getUsername());
		param.put("companyId", req.getUinfo().getCompanyId());
		param.put("remark", remark);
		param.put("memo", req.getReqData().get("OperationMemo"));
		param.put("sqllog", sqllog);
		try {
			PageData sqlInfo = getInsertSQL(conn, req);
			if (sqlInfo.getRetCode() != 0) {
				return sqlInfo;
			}
			StringBuffer log_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			List<String> log_listData = (List<String>) sqlInfo.getData().get("listData");
			update(conn, log_field.toString(), log_listData.toArray());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData addClaimlog(DataReq req, String type, Connection conn) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("拜访信息新增失败");
		param.put("tableName", "log_claim");
		param.put("type", type);
		param.put("uuid", UUID.randomUUID().toString());
		param.put("userid", req.getUinfo().getId());
		param.put("username", req.getUinfo().getUsername());
		param.put("companyId", req.getUinfo().getCompanyId());
		String claimUUID = (String) param.get("uuid");
		if (param.get("visittime").equals("") || "" == param.get("visittime")) {
			param.put("visittime", null);
		}
		try {
			PageData sqlInfo = getInsertSQL(conn, req);
			if (sqlInfo.getRetCode() != 0) {
				return sqlInfo;
			}
			StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			update(conn, sb_field.toString(), listData.toArray());
			SaveOperatelog(req, type, "新增拜访信息", sb_field + "---" + listData, conn);
			param.put("uuid", claimUUID);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData updateClaimlog(DataReq req, String type, Connection conn) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("拜访信息修改失败");
		param.put("tableName", "log_claim");
		param.put("keys", "uuid");
		param.put("uuid", param.get("uuid"));
		param.put("type", type);
		param.put("userid", req.getUinfo().getId());
		param.put("username", req.getUinfo().getUsername());
		param.put("companyId", req.getUinfo().getCompanyId());
		if (param.get("bfsj").equals("") || "" == param.get("bfsj")) {
			param.put("bfsj", null);
		}
		try {
			PageData sqlInfo = getUpdateSQL(conn, req);
			if (sqlInfo.getRetCode() != 0) {
				return sqlInfo;
			}
			StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			update(conn, sb_field.toString(), listData.toArray());
			SaveOperatelog(req, type, "修改拜访信息", sb_field + "---" + listData, conn);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData delClaimlog(DataReq req, String type, Connection conn) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("拜访信息删除失败");
		try {
			String sb = "update log_claim set isdelete='1' where uuid='" + param.get("uuid") + "' and  1=1 ";
			update(conn, sb, new Object[] {});
			SaveOperatelog(req, type, "删除拜访信息", sb, conn);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	// 根据日期类型获取查询条件
	public String dateTypeCondition(String dateType) {
		StringBuffer sbform = new StringBuffer();
		sbform.delete(0, sbform.length());
		switch (dateType) {
		case "01": // 今天

			sbform.append(" and a.createTime between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "02": // 昨天
			sbform.append(" and a.createTime between SUBDATE(curdate(),INTERVAL 1 day) and curdate() ");
			break;
		case "03": // 最近7天
			sbform.append(
					" and a.createTime between SUBDATE(curdate(),INTERVAL 1 week) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "04": // 最近30天
			sbform.append(
					" and a.createTime between SUBDATE(curdate(),INTERVAL 1 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "05": // 最近3个月
			sbform.append(
					" and a.createTime between SUBDATE(curdate(),INTERVAL 3 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "06": // 最近1年
			sbform.append(
					" and a.createTime between SUBDATE(curdate(),INTERVAL 1 year) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		default: // 今天
			sbform.append(" and a.createTime between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		}
		return sbform.toString();
	}

	public String dateTypeCondition_log(String dateType) {
		StringBuffer sbform = new StringBuffer();
		sbform.delete(0, sbform.length());
		switch (dateType) {
		case "01": // 今天
			sbform.append(" and logdate between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "02": // 昨天
			sbform.append(" and logdate between SUBDATE(curdate(),INTERVAL 1 day) and curdate() ");
			break;
		case "03": // 最近7天
			sbform.append(
					" and logdate between SUBDATE(curdate(),INTERVAL 1 week) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "04": // 最近30天
			sbform.append(
					" and logdate between SUBDATE(curdate(),INTERVAL 1 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "05": // 最近3个月
			sbform.append(
					" and logdate between SUBDATE(curdate(),INTERVAL 3 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "06": // 最近1年
			sbform.append(
					" and logdate between SUBDATE(curdate(),INTERVAL 1 year) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		default: // 今天
			sbform.append(" and logdate between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		}
		return sbform.toString();
	}

	public int update(Connection conn, String sql, Object[] obj) throws SQLException {
		PreparedStatement ps = null;
		int cnt = 0;
		try {
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			if (obj != null && obj.length > 0) {
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i + 1, obj[i]);
				}
			}
			cnt = ps.executeUpdate();
		}  finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return cnt;
	}

	public int queryForInt(Connection conn, String sql, Object[] obj) throws SQLException {
		int cnt = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			if (obj != null && obj.length > 0) {
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i + 1, obj[i]);
				}
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				cnt = rs.getInt(1);
			}
		}  finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
		return cnt;
	}

	public List<Map<String, Object>> queryForList(Connection conn, String sql, Object[] obj) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			if (obj != null && obj.length > 0) {
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i + 1, obj[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				for (int k = 1; k <= columnCount; k++) {
					map.put(rsmd.getColumnLabel(k), rs.getObject(k));
				}
				list.add(map);
			}
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
		return list;
	}

	// 根据日期类型获取查询条件
	public String getDateType(String dateType, String column) {
		StringBuffer sbform = new StringBuffer();
		sbform.delete(0, sbform.length());
		switch (dateType) {
		case "01": // 今天
			sbform.append(" and ").append(column).append(" between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "02": // 昨天
			sbform.append(" and ").append(column).append(" between SUBDATE(curdate(),INTERVAL 1 day) and curdate() ");
			break;
		case "03": // 最近7天
			sbform.append(" and ").append(column)
					.append(" between SUBDATE(curdate(),INTERVAL 1 week) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "04": // 最近30天
			sbform.append(" and ").append(column)
					.append(" between SUBDATE(curdate(),INTERVAL 1 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "05": // 最近3个月
			sbform.append(" and ").append(column)
					.append(" between SUBDATE(curdate(),INTERVAL 3 month) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		case "06": // 最近1年
			sbform.append(" and ").append(column)
					.append(" between SUBDATE(curdate(),INTERVAL 1 year) and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		default: // 今天
			sbform.append(" and ").append(column).append(" between curdate() and ADDDATE(curdate(),INTERVAL 1 day) ");
			break;
		}
		return sbform.toString();
	}
	
	public static long updateReutrnId(Connection conn, String sql, Object[] obj) throws SQLException {
		PreparedStatement ps = null;
		long autoIncKeyFromApi = -1; 
		try {
			ps = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			if (obj != null && obj.length > 0) {
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i + 1, obj[i]);
				}
			}
			ps.executeUpdate(); 
			ResultSet rs = ps.getGeneratedKeys(); 
			if (rs.next()) { 
				autoIncKeyFromApi = rs.getInt(1); 
			} 
		}  finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return autoIncKeyFromApi ;
	}
	
}
